import pymysql

# conn = pymysql.connect(host='localhost', user="root", passwd="f5a10ef2dd4f5e90", database="userLogin")
conn = pymysql.connect(host='mysql', user="root", passwd="123456")
cursor = conn.cursor()

print("插入数据库！")
cursor.execute('CREATE DATABASE IF NOT EXISTS dormSys DEFAULT CHARSET utf8 COLLATE utf8_general_ci;')


conn.select_db('dormSys')


print("插入users用户表！")
cursor.execute('drop table if exists users')
sql_users = "CREATE TABLE IF NOT EXISTS `users` (`id` INT NOT NULL AUTO_INCREMENT, `userName` char(255) NOT NULL UNIQUE,`userPswd` varchar(255) NOT NULL, `loginState` INT NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0"
cursor.execute(sql_users)


print("在宿舍信息表中插入相应数据！")
sql_insertDorms = "insert into dorms(dormNumber, floorNumber, roomNumber, bedNumber, state) values('E1', 1, '1', 1, 1)"
cursor.execute(sql_insertDorms)
conn.commit()


conn.close()
cursor.close()